/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pkg_group5;

import java.sql.*;
import java.util.*;

/**
 *
 * @author mattcarr
 */
public class WishListDatabaseFunctions extends Database {

    public WishListDatabaseFunctions() {
        super("wishlists");
    }

    public int registerBookstore(String login, String pass) {

        String query = "INSERT INTO Bookstores (BookstoreName, Password) VALUES (?, ?)";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, login);
            ps.setString(2, pass);

            int register = ps.executeUpdate();

            if (register == 1) {
                return 0;
            } else {
                return 1;
            }

        } catch (java.sql.SQLException e) {
            System.out.println(e);
            return -1;
        }
    }

    public int connectBookstore(String login, String pass) {
        String query = "SELECT BookstoreID FROM Bookstores WHERE BookstoreName =? AND Password =?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, login);
            ps.setString(2, pass);

            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                return rs.getInt(1);
            }

            return -2;
        } catch (java.sql.SQLException e) {
            System.out.println(e);
            return -1;
        }
    }

    public int createWishList(int bookstoreID, int userID) {

        String query = "INSERT INTO WishLists (BookstoreID, User_ID) VALUES (?, ?)";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, bookstoreID);
            ps.setInt(2, userID);

            int wishlist = ps.executeUpdate();

            if (wishlist == 1) {
                return 0;
            } else {
                return -1;
            }

        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }

        return -1;

    }

    public boolean wishListExists(int bookstoreID, int userID) {

        String query = "Select * from WishLists Where User_ID = ? AND BookstoreID = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, userID);
            ps.setInt(2, bookstoreID);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            System.out.println(e);
        }

        return false;
    }

    public int addItemToWishlist(int userID, int bookstoreItemID, int bookstoreID) {

        if (wishListExists(bookstoreID, userID) == false) {
            if (createWishList(bookstoreID, userID) == -1) {
                return -1;
            }
        }

        String query = "INSERT INTO ItemsInWishlist (ItemID, WishlistID) "
                + "SELECT i.ItemID, w.WishlistID "
                + "FROM WishLists w, Items i "
                + "WHERE w.User_ID = ? AND i.BookstoreItemID = ? AND i.BookstoreID = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, userID);
            ps.setInt(2, bookstoreItemID);
            ps.setInt(3, bookstoreID);

            int addItemToWishlist = ps.executeUpdate();

            if (addItemToWishlist == 1) {
                return 0;
            } else {
                return -2;
            }
        } catch (java.sql.SQLException e) {
            System.out.println(e);
            return -1;
        }
    }

    public int removeItemFromWishlist(int userID, int bookstoreItemID, int bookstoreID) {

        String query = "DELETE iW FROM ItemsInWishlist iW, Items i, WishLists w "
                + "WHERE iW.ItemID = i.ItemID "
                + "AND iW.WishlistID = w.WishlistID "
                + "AND w.User_ID = ? "
                + "AND w.BookstoreID = ? "
                + "AND i.BookstoreItemID = ? "
                + "AND i.BookstoreID = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, userID);
            ps.setInt(2, bookstoreID);
            ps.setInt(3, bookstoreItemID);
            ps.setInt(4, bookstoreID);

            int delete = ps.executeUpdate();

            if (delete == 1) {
                return 0;
            } else {
                return -2;
            }
        } catch (/*java.sql.SQL*/Exception e) {
            System.out.println(e);
            return -1;
        }
    }

    public ArrayList<Integer> getItemsInWishlist(int userID, int bookstoreID) {
        String query = "SELECT BookstoreItemID FROM ItemsInWishlist iW, WishLists w, Items i "
                + "WHERE iW.ItemID = i.ItemID AND iW.WishlistID = w.WishListID "
                + "AND i.BookstoreID = ? "
                + "AND w.BookstoreID = ? AND w.User_ID = ?";

        ArrayList<Integer> items = new ArrayList<Integer>();

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, bookstoreID);
            ps.setInt(2, bookstoreID);
            ps.setInt(3, userID);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                items.add(rs.getInt(1));
            }
        } catch (Exception e) {
            System.out.println(e);
        }

        return items;
    }

    public int addItem(int bookstoreID, int bookstoreItemID) {

        if (itemExistsInDatabase(bookstoreItemID, bookstoreID) == false) {
            String query = "INSERT INTO Items (BookstoreID, BookstoreItemID) VALUES (?, ?)";

            try {
                PreparedStatement ps = conn.prepareStatement(query);
                ps.clearParameters();
                ps.setInt(1, bookstoreID);
                ps.setInt(2, bookstoreItemID);

                int addItem = ps.executeUpdate();

                if (addItem == 1) {
                    return 0;
                } else {
                    return -1;
                }
            } catch (java.sql.SQLException e) {
                System.out.println("ERROR HERE:" + e);
            }
        } else {
            //item already exists
            return 0;
        }

        return -1;
    }

    public int deleteItem(int bookstoreID, int bookstoreItemID) {

        String query = "DELETE iW, i "
                + "FROM ItemsInWishlist iW, Items i "
                + "WHERE iw.ItemID = i.ItemID "
                + "AND i.BookstoreID = ? "
                + "AND i.BookstoreItemID = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, bookstoreID);
            ps.setInt(2, bookstoreItemID);

            int deleteItem = ps.executeUpdate();

            if (deleteItem == 1) {
                return 0;
            } else {
                return -1;
            }
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }

        return -1;
    }

    public boolean itemExistsInDatabase(int bookstoreItemID, int bookstoreID) {
        String query = "SELECT ItemID from Items where BookstoreItemID = ? AND BookstoreID = ?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, bookstoreItemID);
            ps.setInt(2, bookstoreID);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            System.out.println(e);
        }

        return false;
    }

    public int getMostWantedItem(int bookstoreID) {

        String query = "SELECT iW.ItemID, COUNT(iW.ItemID) "
                + "FROM ItemsInWishlist iW, Items i, WishLists w "
                + "WHERE i.ItemID = iW.ItemID "
                + "AND w.WishlistID = iW.WishlistID "
                + "AND w.BookstoreID = ? "
                + "AND i.BookstoreID = ? "
                + "GROUP BY iW.ItemID "
                + "ORDER BY COUNT(iW.ItemID) DESC";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, bookstoreID);
            ps.setInt(2, bookstoreID);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }

        return -1;
    }
}
